//
//  WKFMDBTools.m
//  WeKeTang
//
//  Created by ichina on 2017/9/20.
//  Copyright © 2017年 Dezhi Tiancheng Education Technology (Beijing) Co. Ltd. All rights reserved.
//

#import "WKFMDBTools.h"
#import "WKDownLoadModel.h"
#import "WKWatchModel.h"

#define kDefaultDBName @"DownLoad.sqlite"

static WKFMDBTools * _shareDBTools;

@interface WKFMDBTools ()

@property(nonatomic,copy) NSString *  dbName;

@property(nonatomic,strong) FMDatabaseQueue  *databaseQueue;

@end

@implementation WKFMDBTools

+ (instancetype)defaultFMDBToos{
    
    static dispatch_once_t onceToken;
    
    dispatch_once(&onceToken, ^{
        
        _shareDBTools = [[WKFMDBTools alloc]init];
        
    });
    return _shareDBTools;
}

-(instancetype)init{
    if (self = [super init]) {
        //初始化数据库表
        int staue = [self initializeDBWithName:kDefaultDBName];
        
        if (staue == -1) {
            NSLog(@"数据库初始化失败");
        }else{
            NSLog(@"数据库初始化成功");
        }
    }
    
    return self;
}
#pragma mark - 初始化数据库
- (int)initializeDBWithName:(NSString *)name{
    if (!name) {
        return -1;  // 返回数据库创建失败
    }
    // 沙盒Docu目录
    NSString * docp = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) objectAtIndex:0];
    _dbName = [docp stringByAppendingString:[NSString stringWithFormat:@"/%@",name]];
    NSLog(@"数据库路径：%@",NSHomeDirectory());
    NSFileManager * fileManager = [NSFileManager defaultManager];
    BOOL exist = [fileManager fileExistsAtPath:_dbName];
    [self connect];
    if (!exist) {
        return 0;
    } else {
        return 1;          // 返回 数据库已经存在
    }
}
#pragma mark - 连接数据库
- (void)connect{
    if (!_databaseQueue) {
        _databaseQueue = [[FMDatabaseQueue alloc]initWithPath:_dbName];
    }
}
#pragma mark -  创建表格
-(void)creatTableDownload{
    
    [_databaseQueue inDatabase:^(FMDatabase *db) {
        BOOL sucess = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS DownloadList (vurl text, cgrade text, section_name text, section_imgurl text, section_videoid text, courseid text, stimes text, status text, currentWatchCount text, totalWatchCount text, completeCount text, totalCount text, currentUrl text);"];
        if (sucess) {
            NSLog(@"下载表创建成功");
        }else{
            NSLog(@"下载表创建失败");
        }
    }];
}
-(void)creatTableWatchRecode{
    
    [_databaseQueue inDatabase:^(FMDatabase *db) {
        BOOL sucess = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS WatchRecodeList (vurl text, cgrade text, section_name text, section_imgurl text, section_videoid text, courseid text, stimes text, status text, totalTime text, currentTime text);"];
        if (sucess) {
            NSLog(@"观看表创建成功");
        }else{
            NSLog(@"观看表创建失败");
        }
    }];
}
#pragma mark - 插入下载数据
-(void)InsertDataWithVurl:(NSString *)vurl
                   Cgrade:(NSString*)cgrade
             section_name:(NSString*)section_name
           section_imgurl:(NSString*)section_imgurl
          section_videoid:(NSString*)section_videoid
                 courseid:(NSString*)courseid
                   stimes:(NSString*)stimes
                   status:(NSString*)status
        currentWatchCount:(NSString*)currentWatchCount
          totalWatchCount:(NSString*)totalWatchCount
            completeCount:(NSString*)completeCount
               totalCount:(NSString*)totalCount
               currentUrl:(NSString*)currentUrl{
    
    static NSString *downLoadSQL = @"INSERT OR REPLACE INTO DownloadList ('vurl','cgrade','section_name','section_imgurl','section_videoid','courseid','stimes','status','currentWatchCount','totalWatchCount','completeCount','totalCount','currentUrl') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    [_databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        [db executeUpdate:downLoadSQL,vurl,cgrade,section_name,section_imgurl,section_videoid,courseid,stimes,status,currentWatchCount,totalWatchCount,completeCount,totalCount,currentUrl];
    }];
    
}
#pragma mark - 插入观看数据
-(void)InsertWatchDataWithVurl:(NSString *)vurl
                   Cgrade:(NSString*)cgrade
             section_name:(NSString*)section_name
           section_imgurl:(NSString*)section_imgurl
          section_videoid:(NSString*)section_videoid
                 courseid:(NSString*)courseid
                   stimes:(NSString*)stimes
                   status:(NSString*)status
                totalTime:(NSString*)totalTime
              currentTime:(NSString*)currentTime{
    
    static NSString *downLoadSQL = @"INSERT OR REPLACE INTO WatchRecodeList ('vurl','cgrade','section_name','section_imgurl','section_videoid','courseid','stimes','status','totalTime','currentTime') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    [_databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        [db executeUpdate:downLoadSQL,vurl,cgrade,section_name,section_imgurl,section_videoid,courseid,stimes,status,totalTime,currentTime];
    }];
}

#pragma mark - 查询数据
-(NSMutableArray*)QueryDataWithKey:(NSString*)key{
    
    NSMutableArray *mArray = [NSMutableArray array];
    
    [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        
        FMResultSet *result =  [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM '%@' ",key]];
        while ([result next]) {
            WKDownLoadModel *model = [[WKDownLoadModel alloc]init];
            model.vurl = [result stringForColumn:@"vurl"];
            model.cgrade = [result stringForColumn:@"cgrade"];
            model.section_name = [result stringForColumn:@"section_name"];
            model.section_imgurl = [result stringForColumn:@"section_imgurl"];
            model.section_videoid = [result stringForColumn:@"section_videoid"];
            model.courseid = [result stringForColumn:@"courseid"];
            model.stimes = [result stringForColumn:@"stimes"];
            model.status = [result stringForColumn:@"status"];
            model.completeCount = [result stringForColumn:@"completeCount"];
            model.totalCount = [result stringForColumn:@"totalCount"];
            model.currentUrl = [result stringForColumn:@"currentUrl"];
            [mArray addObject:model];
        }
    }];
    
    return mArray;
}
#pragma mark - 查询观看记录
-(NSMutableArray*)QueryWatchDataWithKey:(NSString*)key{
    
    NSMutableArray *mArray = [NSMutableArray array];
    
    [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        
        FMResultSet *result =  [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM '%@' ",key]];
        while ([result next]) {
            WKWatchModel *model = [[WKWatchModel alloc]init];
            model.vurl = [result stringForColumn:@"vurl"];
            model.cgrade = [result stringForColumn:@"cgrade"];
            model.section_name = [result stringForColumn:@"section_name"];
            model.section_imgurl = [result stringForColumn:@"section_imgurl"];
            model.section_videoid = [result stringForColumn:@"section_videoid"];
            model.courseid = [result stringForColumn:@"courseid"];
            model.stimes = [result stringForColumn:@"stimes"];
            model.status = [result stringForColumn:@"status"];
            model.totalTime = [result stringForColumn:@"totalTime"];
            model.currentTime = [result stringForColumn:@"currentTime"];
            [mArray addObject:model];
        }
    }];
    
    return mArray;
}

#pragma mark - 根据状态查询数据
-(NSMutableArray*)QueryDataWithTableName:(NSString*)tableName Key:(NSString*)key{
    
    NSMutableArray *mArray = [NSMutableArray array];
    
    [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        
        FMResultSet *result =  [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM '%@' WHERE status = '%@' ",tableName,key]];
        while ([result next]) {
            WKDownLoadModel *model = [[WKDownLoadModel alloc]init];
            model.vurl = [result stringForColumn:@"vurl"];
            model.cgrade = [result stringForColumn:@"cgrade"];
            model.section_name = [result stringForColumn:@"section_name"];
            model.section_imgurl = [result stringForColumn:@"section_imgurl"];
            model.section_videoid = [result stringForColumn:@"section_videoid"];
            model.courseid = [result stringForColumn:@"courseid"];
            model.stimes = [result stringForColumn:@"stimes"];
            model.status = [result stringForColumn:@"status"];
            model.currentWatchCount = [result stringForColumn:@"currentWatchCount"];
            model.totalWatchCount = [result stringForColumn:@"totalWatchCount"];
            model.completeCount = [result stringForColumn:@"completeCount"];
            model.totalCount = [result stringForColumn:@"totalCount"];
            model.currentUrl = [result stringForColumn:@"currentUrl"];
            [mArray addObject:model];
        }
    }];
    
    return mArray;
}
#pragma mark - 查询下载数据
-(NSMutableArray*)QueryDataWithTableName:(NSString*)tableName
                         section_videoid:(NSString*)section_videoid{
    
    NSMutableArray *mArray = [NSMutableArray array];
    
    [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        
        FMResultSet *result =  [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM '%@' WHERE section_videoid = '%@' ",tableName,section_videoid]];
        while ([result next]) {
            WKDownLoadModel *model = [[WKDownLoadModel alloc]init];
            model.vurl = [result stringForColumn:@"vurl"];
            model.cgrade = [result stringForColumn:@"cgrade"];
            model.section_name = [result stringForColumn:@"section_name"];
            model.section_imgurl = [result stringForColumn:@"section_imgurl"];
            model.section_videoid = [result stringForColumn:@"section_videoid"];
            model.courseid = [result stringForColumn:@"courseid"];
            model.stimes = [result stringForColumn:@"stimes"];
            model.status = [result stringForColumn:@"status"];
            model.completeCount = [result stringForColumn:@"completeCount"];
            model.totalCount = [result stringForColumn:@"totalCount"];
            model.currentUrl = [result stringForColumn:@"currentUrl"];
            [mArray addObject:model];
        }
    }];
    
    return mArray;
}
#pragma mark - 查询下载未完成的数据
-(NSMutableArray*)QueryDownloadingDataWithTableName:(NSString*)tableName
                         status:(NSString*)status{
    
    NSMutableArray *mArray = [NSMutableArray array];
    
    [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        
        FMResultSet *result =  [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM '%@' WHERE status = '%@' ",tableName,status]];
        while ([result next]) {
            WKDownLoadModel *model = [[WKDownLoadModel alloc]init];
            model.vurl = [result stringForColumn:@"vurl"];
            model.cgrade = [result stringForColumn:@"cgrade"];
            model.section_name = [result stringForColumn:@"section_name"];
            model.section_imgurl = [result stringForColumn:@"section_imgurl"];
            model.section_videoid = [result stringForColumn:@"section_videoid"];
            model.courseid = [result stringForColumn:@"courseid"];
            model.stimes = [result stringForColumn:@"stimes"];
            model.status = [result stringForColumn:@"status"];
            model.completeCount = [result stringForColumn:@"completeCount"];
            model.totalCount = [result stringForColumn:@"totalCount"];
            model.currentUrl = [result stringForColumn:@"currentUrl"];
            [mArray addObject:model];
        }
    }];
    
    return mArray;
}
#pragma mark - 查询播放数据
-(NSMutableArray*)QueryWatchDataWithTableName:(NSString*)tableName
                         section_videoid:(NSString*)section_videoid{
    
    NSMutableArray *mArray = [NSMutableArray array];
    
    [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        
        FMResultSet *result =  [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM '%@' WHERE section_videoid = '%@' ",tableName,section_videoid]];
        while ([result next]) {
            WKWatchModel *model = [[WKWatchModel alloc]init];
            model.vurl = [result stringForColumn:@"vurl"];
            model.cgrade = [result stringForColumn:@"cgrade"];
            model.section_name = [result stringForColumn:@"section_name"];
            model.section_imgurl = [result stringForColumn:@"section_imgurl"];
            model.section_videoid = [result stringForColumn:@"section_videoid"];
            model.courseid = [result stringForColumn:@"courseid"];
            model.stimes = [result stringForColumn:@"stimes"];
            model.status = [result stringForColumn:@"status"];
            model.currentTime = [result stringForColumn:@"currentTime"];
            model.totalTime = [result stringForColumn:@"totalTime"];
            [mArray addObject:model];
        }
    }];
    
    return mArray;
}
#pragma mark - 查询播放记录
-(NSString*)QueryWatchSeekTimeWithTableName:(NSString*)tableName
                            section_videoid:(NSString*)section_videoid{
   __block NSString *str;
    [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        
        FMResultSet *result =  [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM '%@' WHERE section_videoid = '%@' ",tableName,section_videoid]];
        while ([result next]) {
            WKWatchModel *model = [[WKWatchModel alloc]init];
            model.currentTime = [result stringForColumn:@"currentTime"];
            str = model.currentTime;
        }
    }];
    
    return str;
}

#pragma mark - 更新数据库记录的下载进度
-(void)UpdateDataWithTabelName:(NSString *)TabelName
                    completeCount:(NSString*)completeCount
               section_videoid:(NSString*)section_videoid{
    
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE %@ SET completeCount = '%@' WHERE section_videoid = '%@'",TabelName,completeCount,section_videoid];
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdate:updateSQL];
        if (!result) {
            NSLog(@"更新完成数量失败");
        } else {
            NSLog(@"更新完成数量成功");
        }
        
    }];
    
}
#pragma mark - 下载完成更新状态
-(void)UpdateDataWithTabelName:(NSString *)TabelName
                        status:(NSString*)status
               section_videoid:(NSString*)section_videoid{
    
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE %@ SET status = '%@' WHERE section_videoid = '%@'",TabelName,status,section_videoid];
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdate:updateSQL];
        if (!result) {
            NSLog(@"更新状态失败");
        } else {
            NSLog(@"更新状态成功");
        }
        
    }];
    
}
#pragma mark - 更新当前下载的url
-(void)UpdateDataWithTabelName:(NSString *)TabelName
                 currentUrl:(NSString*)currentUrl
               section_videoid:(NSString*)section_videoid{
    
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE %@ SET currentUrl = '%@' WHERE section_videoid = '%@'",TabelName,currentUrl,section_videoid];
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdate:updateSQL];
        if (!result) {
            NSLog(@"更新url失败");
        } else {
            NSLog(@"更新url成功");
        }
        
    }];
    
}
#pragma mark - 更新播放记录数据
-(void)UpdateWatchDataWithTabelName:(NSString *)TabelName
                          vurl:(NSString*)vurl
                        cgrade:(NSString*)cgrade
                  section_name:(NSString*)section_name
                section_imgurl:(NSString*)section_imgurl
                      courseid:(NSString*)courseid
                        stimes:(NSString*)stimes
                        status:(NSString*)status
                     totalTime:(NSString*)totalTime
                   currentTime:(NSString*)currentTime
               section_videoid:(NSString*)section_videoid{
    
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE %@ SET currentTime = '%@' WHERE section_videoid = '%@'",TabelName,currentTime,section_videoid];
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdate:updateSQL];
        if (!result) {
            NSLog(@"更新完成数量失败");
        } else {
            NSLog(@"更新完成数量成功");
        }
        
    }];
    
}
#pragma mark - 更新下载功能的播放
-(void)UpdateDownloadWatchDataWithTabelName:(NSString *)TabelName
                          currentWatchCount:(NSString*)currentWatchCount
                            totalWatchCount:(NSString*)totalWatchCount
                            section_videoid:(NSString*)section_videoid{
    
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE %@ SET currentWatchCount = '%@', totalWatchCount = '%@' WHERE section_videoid = '%@'",TabelName,currentWatchCount,totalWatchCount,section_videoid];
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdate:updateSQL];
        if (!result) {
            NSLog(@"更新完成数量失败");
        } else {
            NSLog(@"更新完成数量成功");
        }
        
    }];
    
}
#pragma mark - 删除下载视频
-(void)delegateDownLoadVideoWithTableName:(NSString*)tableName section_videoid:(NSString*)section_videoid{
    [_databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
       BOOL result = [db executeUpdate:[NSString stringWithFormat:@"DELETE FROM %@ WHERE section_videoid= '%@'",tableName,section_videoid]];
        if (!result) {
            NSLog(@"删除视频失败");
        } else {
            NSLog(@"删除视频成功");
        }
        
    }];
}
#pragma mark - 删除观看数据
-(void)delegateWatchVideoWithTableName:(NSString*)tableName section_videoid:(NSString*)section_videoid{
    [_databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL result = [db executeUpdate:[NSString stringWithFormat:@"DELETE FROM %@ WHERE section_videoid= '%@'",tableName,section_videoid]];
        if (!result) {
            NSLog(@"删除观看视频失败");
        } else {
            NSLog(@"删除观看视频成功");
        }
        
    }];
}
#pragma mark - 删除数据
-(void)delegateWithKey:(NSString*)key{
    
    [_databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        [db executeUpdate:[NSString stringWithFormat:@"DELETE FROM %@",key]];
    }];
}
#pragma mark - 数据库是否打开
-(BOOL)isOpenDatabese:(FMDatabase *)db{
    
    if (![db open]) {
        [db open];
    }
    return YES;
}
#pragma mark - 关闭数据库
-(void)close{
    
    [_databaseQueue close];
    _shareDBTools = nil;
    
}

-(void)dealloc{
    
    [self close];
}
@end
